package rkdwogus7.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import rkdwogus7.interfaces.SungJukService;
import rkdwogus7.model.SungJukVO;

public class JDBCSungJuk implements SungJukService {
	
	// JDBC 접속 정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 작성
	// update, delete문도 작성해 두고 나중에는 String도 + 말고 buffer나 builder를 사용하게 될거야.
	// 저장 procedure를 활용하는 방법도 알고 있다면 장난 아니겠지.
	// 정말 나중에는 mybatis 나 hibernate 를 이용하면 돼
	// 또한 String을 연결시켰을 경우에는 " <- 이 빈 칸 잊지 말도록. 앞 뒤로 넣을 것을 권장해.
	private static final String insertSQL = "insert into sungjuk" + "(sjno, name, kor, eng, mat)"
		 + "VALUES (SJNO.nextval, ?, ?, ?, ?) ";
	// 입력받아서 처리하게끔 만드는 것을 또 할거야.
	private static final String selectSQL = " select * from sungjuk order by sjno desc";
	private static final String updateSQL = " update sungjuk set name = ?, kor = ?, eng = ?, mat = ? where sjno = ?";
	private static final String deleteSQL = " delete from sungjuk where sjno = ? ";

	@Override
	public boolean newSungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try {
			Class.forName( DRV );
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			// ? 매개변수에 들어갈 실제 입력값을 지정
				ps.setString(1, sj.getName());
				ps.setInt(2, sj.getKor());
				ps.setInt(3, sj.getEng());
				ps.setInt(4, sj.getMat());
			
			
			
			int cnt = ps.executeUpdate();
			// 성공적으로 insert 되면 result를 true로
			if ( cnt > 0 ) { result = true; }
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) 
				try { ps.close(); } catch (Exception e) { }
			if (conn != null) 
				try { conn.close(); } catch (Exception e) { }
		}
		
		return result;
	}

	@Override
	public String readSungJuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// selectSQL 실행결과를 모두 출력
			while(rs.next()) {
				for (int i = 1; i <= 9; ++i) {
					result += rs.getString(i) + " ";
				}
				result += "\n";
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				{try{ rs.close(); } catch(Exception e) { }
				}
			if (ps != null)
				try{ ps.close(); } catch(Exception e) { }
			if (conn != null)
				try{ conn.close(); } catch(Exception e) { }
		}
		
		return result;
	}

	@Override
	public boolean modifySungJuk(SungJukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		// 성공적으로 수정 되었는지 확인용
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());
			ps.setString(5, sj.getSjno());
			
			int cnt = ps.executeUpdate();
			if (cnt > 0) { result = true; }
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		return result;
	}

	@Override
	public boolean removeSungJuk(String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		// 성공적으로 삭제 되었는지 확인용
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement( deleteSQL );
			ps.setString(1, key);	// 첫 번째 ?에 들어갈 값 지정
			
			int cnt = ps.executeUpdate();
			// sql문 실행 후 결과 반환
			if (cnt > 0) { result = true; }
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e ) { }
			}
			
		}
		
		return result;
	}

}
